package exttable.dbchecking;

import dbutils.DbUtils;
import dbutils.StringQueries;
import exttable.SGTTestParametrs;
import exttable.dbclasses.DbTable;
import exttable.formgenerator.MessageFactory;
import exttable.formgenerator.StaticMethods;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import utils.Localisation;

import javax.swing.*;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by IntelliJ IDEA.
 * User: ADMIN
 * Date: 12.08.11
 * Time: 17:04
 * To change this template use File | Settings | File Templates.
 */
public class TestMethods {

    public static ResultSet getTestOfAnalysisTranslation() {

        try {
            Connection connection = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = connection.prepareStatement(StringQueries.TEST_OF_ANALYSIS_TRANSLATION);
            ResultSet rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
//            for (int i = 0; i < rsmd.getColumnCount(); i++) {
//                System.out.println(rsmd.getColumnName(i + 1));
//            }
            return rs;
        } catch (Exception exp) {
            exp.printStackTrace();
        }

        return null;
    }

    public static ResultSet getTestOfTestParametrsUnits() {
        try {
            Connection connection = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = connection.prepareStatement(StringQueries.TEST_OF_PARAMETERS_UNITS);
            ResultSet rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            for (int i = 0; i < rsmd.getColumnCount(); i++) {
                System.out.println(rsmd.getColumnName(i + 1));
            }
            return rs;
        } catch (Exception exp) {
            exp.printStackTrace();
        }
        return null;
    }

    public static List<List<String>> getDataListThatHaveToFillForCompare() {
        /*
        This method return all rows from t1 that does not in t2(t1 and t2 same tables, which differs only by database name)
         */
        List<List<String>> dataList = new ArrayList<List<String>>();
        try {
            Connection conn = DbUtils.getDBConnectionGeneral();
            for (DbTable dbTable : RunDBChecking.mapTable.values()) {
                String query = "select 'GENERAL' as dataBaseName, " +
                        DbUtils.GENERAL_DATABASE_NAME + "." + dbTable.getTableName() + ".test_id from " +
                        DbUtils.GENERAL_DATABASE_NAME + "." + dbTable.getTableName() +
                        " left outer join " +
                        DbUtils.SECONDARY_DATABASE_NAME + "." + dbTable.getTableName() +
                        " on("
                        + DbUtils.GENERAL_DATABASE_NAME + "." + dbTable.getTableName() + ".test_id=" +
                        DbUtils.SECONDARY_DATABASE_NAME + "." + dbTable.getTableName() + ".test_id) " +
                        " where " + DbUtils.SECONDARY_DATABASE_NAME + "." + dbTable.getTableName() + ".test_id is null " +
                        " and " + DbUtils.GENERAL_DATABASE_NAME + "." + dbTable.getTableName() + ".test_id in( " +
                        " SELECT test_id " +
                        " FROM visit_details " +
                        " WHERE probationer_id IN(SELECT probationer_id FROM probationers " +
                        " INNER JOIN groups ON (probationers.group_id=groups.group_id) " +
                        " INNER JOIN dic_phase ON (groups.phase_id = dic_phase.phase_id) " +
                        " WHERE dic_phase.phase_id IN("+StaticMethods.joinPhaseListToString(RunDBChecking.phaseToReturn)+")))"+
                        " UNION " +
                        "select 'SECONDARY' as dataBaseName, " +
                        DbUtils.SECONDARY_DATABASE_NAME + "." + dbTable.getTableName() + ".test_id from " +
                        DbUtils.SECONDARY_DATABASE_NAME + "." + dbTable.getTableName() +
                        " left outer join " +
                        DbUtils.GENERAL_DATABASE_NAME + "." + dbTable.getTableName() +
                        " on("
                        + DbUtils.GENERAL_DATABASE_NAME + "." + dbTable.getTableName() + ".test_id=" +
                        DbUtils.SECONDARY_DATABASE_NAME + "." + dbTable.getTableName() + ".test_id) " +
                        " where " + DbUtils.GENERAL_DATABASE_NAME + "." + dbTable.getTableName() + ".test_id is null " +
                        " and " + DbUtils.SECONDARY_DATABASE_NAME + "." + dbTable.getTableName() + ".test_id in( " +
                        " SELECT test_id " +
                        " FROM visit_details " +
                        " WHERE probationer_id IN(SELECT probationer_id FROM probationers " +
                        " INNER JOIN groups ON (probationers.group_id=groups.group_id) " +
                        " INNER JOIN dic_phase ON (groups.phase_id = dic_phase.phase_id) " +
                        " WHERE dic_phase.phase_id IN("+StaticMethods.joinPhaseListToString(RunDBChecking.phaseToReturn)+")))";
//                System.out.println(query);
                PreparedStatement ps = conn.prepareStatement(query);
                ResultSet rs = ps.executeQuery();

                ResultSet rsGetProbationer = null;
                Connection varConn = null;
                while (rs.next()) {
                    List<String> tempList = new ArrayList<String>();
                    tempList.add(RunDBChecking.mapAntonymDataBaseMode.get(rs.getString("dataBaseName")));
                    tempList.add(dbTable.toString());
                    if (rs.getString("dataBaseName").equals("GENERAL")) {
                        varConn = DbUtils.getDBConnectionGeneral();
                    } else {
                        varConn = DbUtils.getDBConnectionSecondary();
                    }
                    PreparedStatement psGetProbationer = varConn.prepareStatement("SELECT probationers.probationer_code,\n" +
                            "schema_visit_time.time_mls/(24*3600) AS days\n" +
                            "FROM visit_details\n" +
                            "INNER JOIN probationers ON(probationers.probationer_id=visit_details.probationer_id)\n" +
                            "INNER JOIN schema_visit_time ON(schema_visit_time.visit_id=visit_details.visit_id)\n" +
                            "WHERE\n" +
                            "test_id=?\n" +
                            "AND schema_visits_id IN("+
                            " SELECT"+
                            " groups_schema_visits.schema_visits_id"+
                            " FROM groups_schema_visits"+
                            " WHERE groups_schema_visits.groups_schema_visits_id IN("+
                            " SELECT groups_schema_visits_id FROM visit_details"+
                            " WHERE test_id=?)"+
                            ");");
                    psGetProbationer.setString(1, rs.getString("test_id"));
                    psGetProbationer.setString(2, rs.getString("test_id"));
                    rsGetProbationer = psGetProbationer.executeQuery();
                    while (rsGetProbationer.next()) {
                        tempList.add(rsGetProbationer.getString("probationer_code"));
                        tempList.add(String.valueOf(Math.round(Double.parseDouble(rsGetProbationer.getString("days")))));
                    }
                    varConn.close();
                    dataList.add(tempList);
                }
            }
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
        return dataList;
    }

    public static void getThatHaveToFillForCompareExcel() throws IOException {
        List<List<String>> dataList = TestMethods.getDataListThatHaveToFillForCompare();
        if (dataList.size() < 1) {
            TestMethods.getCompareExcel();
        } else {
            JFileChooser jfc = new JFileChooser();
            jfc.setDialogTitle(Localisation.getMessage("form.selectplaceandname"));
            jfc.showSaveDialog(null);
            jfc.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
            File file = jfc.getSelectedFile();

            FileOutputStream out = null;
            Cell cell;
            Row row;
            try {
                out = new FileOutputStream(file.getAbsolutePath() + ".xls");
                Workbook wb = new HSSFWorkbook();
                Sheet s = wb.createSheet();
                int rowNum = 1;
                row = s.createRow(0);

                cell = row.createCell(0);
                cell.setCellValue("DataBase name");

                cell = row.createCell(1);
                cell.setCellValue("Test name");

                cell = row.createCell(2);
                cell.setCellValue("Probationer Code");

                cell = row.createCell(3);
                cell.setCellValue("Day of visit");

                for (List<String> list : dataList) {
                    row = s.createRow(rowNum);

                    cell = row.createCell(0);
                    cell.setCellValue(list.get(0));

                    cell = row.createCell(1);
                    cell.setCellValue(list.get(1));

                    cell = row.createCell(2);
                    cell.setCellValue(list.get(2));

                    cell = row.createCell(3);
                    cell.setCellValue(Integer.valueOf(list.get(3)));

                    rowNum++;
                }
                wb.write(out);
//            StaticMethods.getFrameByName("RunQuestionnaireAnalyser").dispose();
                out.close();
                JOptionPane.showMessageDialog(null, Localisation.getMessage("info.filecreated") + file.getAbsolutePath() + ".xls",
                        Localisation.getMessage("info.title"), JOptionPane.INFORMATION_MESSAGE);
            } catch (java.io.FileNotFoundException fileOpen) {
                MessageFactory.errorToSave();

            } catch (IOException e) {
                e.printStackTrace();
                throw new IOException("error");
            } finally {
                out.close();
            }
        }

    }

    public static void getCompareExcel() throws IOException {
        List<List<String>> dataList = StaticMethods.getDataForCheck(StaticMethods.getLinkedListQueriesFromMap(DbUtils.GENERAL_DATABASE_NAME));
        if (dataList.size() < 1) {
            MessageFactory.getMessageWarnings(false);
        } else {
            JFileChooser jfc = new JFileChooser();
            jfc.setDialogTitle(Localisation.getMessage("form.selectplaceandname"));
            jfc.showSaveDialog(null);
            jfc.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
            File file = jfc.getSelectedFile();

            FileOutputStream out = null;
            Cell cell;
            Row row;
            try {
                out = new FileOutputStream(file.getAbsolutePath() + ".xls");
                Workbook wb = new HSSFWorkbook();
                Sheet s = wb.createSheet();
                int rowNum = 1;
                row = s.createRow(0);

                cell = row.createCell(0);
                cell.setCellValue("Test name");

                cell = row.createCell(1);
                cell.setCellValue("Column Name");

                cell = row.createCell(2);
                cell.setCellValue("Column value of original");

                cell = row.createCell(3);
                cell.setCellValue("Column value of copy");

                cell = row.createCell(4);
                cell.setCellValue("Probationer Code");

                cell = row.createCell(5);
                cell.setCellValue("Day of visit");

                for (List<String> list : dataList) {
                    row = s.createRow(rowNum);

                    cell = row.createCell(0);
                    cell.setCellValue(list.get(1));

                    cell = row.createCell(1);
                    cell.setCellValue(list.get(2));

                    cell = row.createCell(2);
                    cell.setCellValue(list.get(3));

                    cell = row.createCell(3);
                    cell.setCellValue(list.get(4));

                    cell = row.createCell(4);
                    cell.setCellValue(list.get(5));

                    cell = row.createCell(5);
                    if (list.get(0).equals("obj_data_all_by_irk")) {
                        cell.setCellValue(Integer.parseInt(list.get(6 + 2)));
                    } else {
                        cell.setCellValue(Integer.parseInt(list.get(6)));
                    }

                    rowNum++;
                }
                wb.write(out);
//            StaticMethods.getFrameByName("RunQuestionnaireAnalyser").dispose();
                out.close();
                JOptionPane.showMessageDialog(null, Localisation.getMessage("info.filecreated") + file.getAbsolutePath() + ".xls",
                        Localisation.getMessage("info.title"), JOptionPane.INFORMATION_MESSAGE);
            } catch (java.io.FileNotFoundException fileOpen) {
                MessageFactory.errorToSave();

            } catch (IOException e) {
                e.printStackTrace();
                throw new IOException("error");
            } finally {
                out.close();
            }
        }

    }

    public static List<List<String>> getDataListFromResultAnswersThatHaveToFillForCompare() {
        /*
        This method return all rows from result_answers1 that does not in result_answers2(result_answers1 and result_answers2 same tables, which differs only by database name)
         */
        List<List<String>> dataList = new ArrayList<List<String>>();
        try {
            Connection conn = DbUtils.getDBConnectionGeneral();
            String query = "select 'GENERAL' as dataBaseName, " +
                    DbUtils.GENERAL_DATABASE_NAME + ".result_answers.result_answer_id," +
                    DbUtils.GENERAL_DATABASE_NAME + ".result_answers.probationer_id," +
                    DbUtils.GENERAL_DATABASE_NAME + ".probationers.probationer_code," +
                    DbUtils.GENERAL_DATABASE_NAME + ".result_answers.vizit_id," +
                    DbUtils.GENERAL_DATABASE_NAME + ".dic_visits.name_ru," +
                    DbUtils.GENERAL_DATABASE_NAME + ".dic_visits.name_en," +
                    DbUtils.GENERAL_DATABASE_NAME + ".dic_visits.name_kk" +
                    " from " +
                    DbUtils.GENERAL_DATABASE_NAME + ".result_answers " +
                    " inner join " + DbUtils.GENERAL_DATABASE_NAME + ".probationers" +
                    " on("
                    + DbUtils.GENERAL_DATABASE_NAME + ".result_answers.probationer_id=" +
                    DbUtils.GENERAL_DATABASE_NAME + ".probationers.probationer_id) " +
                    " inner join " + DbUtils.GENERAL_DATABASE_NAME + ".dic_visits" +
                    " on("
                    + DbUtils.GENERAL_DATABASE_NAME + ".result_answers.vizit_id=" +
                    DbUtils.GENERAL_DATABASE_NAME + ".dic_visits.vizit_id) " +
                    " left outer join " +
                    DbUtils.SECONDARY_DATABASE_NAME + ".result_answers " +
                    " on("
                    + DbUtils.GENERAL_DATABASE_NAME + ".result_answers.result_answer_id=" +
                    DbUtils.SECONDARY_DATABASE_NAME + ".result_answers.result_answer_id) " +
                    " where " + DbUtils.SECONDARY_DATABASE_NAME + ".result_answers.result_answer_id is null " +
                    " and "+ DbUtils.GENERAL_DATABASE_NAME+".result_answers.probationer_id in(SELECT probationer_id FROM probationers " +
                    " INNER JOIN groups ON (probationers.group_id=groups.group_id) " +
                    " INNER JOIN dic_phase ON (groups.phase_id = dic_phase.phase_id) " +
                    " WHERE dic_phase.phase_id IN("+StaticMethods.joinPhaseListToString(RunDBChecking.phaseToReturn)+"))"+
                    " UNION " +
                    "select 'SECONDARY' as dataBaseName, " +
                    DbUtils.SECONDARY_DATABASE_NAME + ".result_answers.result_answer_id," +
                    DbUtils.SECONDARY_DATABASE_NAME + ".result_answers.probationer_id," +
                    DbUtils.SECONDARY_DATABASE_NAME + ".probationers.probationer_code," +
                    DbUtils.SECONDARY_DATABASE_NAME + ".result_answers.vizit_id," +
                    DbUtils.SECONDARY_DATABASE_NAME + ".dic_visits.name_ru," +
                    DbUtils.SECONDARY_DATABASE_NAME + ".dic_visits.name_en," +
                    DbUtils.SECONDARY_DATABASE_NAME + ".dic_visits.name_kk" +
                    " from " +
                    DbUtils.SECONDARY_DATABASE_NAME + ".result_answers" +
                    " inner join " + DbUtils.SECONDARY_DATABASE_NAME + ".probationers" +
                    " on("
                    + DbUtils.SECONDARY_DATABASE_NAME + ".result_answers.probationer_id=" +
                    DbUtils.SECONDARY_DATABASE_NAME + ".probationers.probationer_id) " +
                    " inner join " + DbUtils.SECONDARY_DATABASE_NAME + ".dic_visits" +
                    " on("
                    + DbUtils.SECONDARY_DATABASE_NAME + ".result_answers.vizit_id=" +
                    DbUtils.SECONDARY_DATABASE_NAME + ".dic_visits.vizit_id) " +
                    " left outer join " +
                    DbUtils.GENERAL_DATABASE_NAME + ".result_answers" +
                    " on("
                    + DbUtils.GENERAL_DATABASE_NAME + ".result_answers.result_answer_id=" +
                    DbUtils.SECONDARY_DATABASE_NAME + ".result_answers.result_answer_id) " +
                    " where " + DbUtils.GENERAL_DATABASE_NAME + ".result_answers.result_answer_id is null "+
                    " and "+ DbUtils.GENERAL_DATABASE_NAME+".result_answers.probationer_id in(SELECT probationer_id FROM probationers " +
                    " INNER JOIN groups ON (probationers.group_id=groups.group_id) " +
                    " INNER JOIN dic_phase ON (groups.phase_id = dic_phase.phase_id) " +
                    " WHERE dic_phase.phase_id IN("+StaticMethods.joinPhaseListToString(RunDBChecking.phaseToReturn)+"))";
//            System.out.println("query :"+query);
            PreparedStatement ps = conn.prepareStatement(query);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                List<String> tempList = new ArrayList<String>();
                tempList.add(RunDBChecking.mapAntonymDataBaseMode.get(rs.getString("dataBaseName")));
                tempList.add("result_answers");
                tempList.add(rs.getString("probationer_id"));
                tempList.add(rs.getString("probationer_code"));
                tempList.add(rs.getString("vizit_id"));
                tempList.add(rs.getString(SGTTestParametrs.getInstance().getCurrentLocal().getColumn()));
                dataList.add(tempList);
            }
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
        return dataList;
    }

    public static void getThatHaveToFillToResultAnswersForCompareExcel() throws IOException {
        List<List<String>> dataList = TestMethods.getDataListFromResultAnswersThatHaveToFillForCompare();
        if (dataList.size() < 1) {
            TestMethods.getCompareExcelForResultAnswers();
        } else {
            JFileChooser jfc = new JFileChooser();
            jfc.setDialogTitle(Localisation.getMessage("form.selectplaceandname"));
            jfc.showSaveDialog(null);
            jfc.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
            File file = jfc.getSelectedFile();

            FileOutputStream out = null;
            Cell cell;
            Row row;
            try {
                out = new FileOutputStream(file.getAbsolutePath() + ".xls");
                Workbook wb = new HSSFWorkbook();
                Sheet s = wb.createSheet();
                int rowNum = 1;
                row = s.createRow(0);

                cell = row.createCell(0);
                cell.setCellValue("DataBase name");

                cell = row.createCell(1);
                cell.setCellValue("Test name");

                cell = row.createCell(2);
                cell.setCellValue("Probationer Id");

                cell = row.createCell(3);
                cell.setCellValue("Probationer Code");

                cell = row.createCell(4);
                cell.setCellValue("Visit ID");

                cell = row.createCell(5);
                cell.setCellValue("Visit");

                for (List<String> list : dataList) {
                    row = s.createRow(rowNum);

                    cell = row.createCell(0);
                    cell.setCellValue(list.get(0));

                    cell = row.createCell(1);
                    cell.setCellValue(list.get(1));

                    cell = row.createCell(2);
                    cell.setCellValue(Integer.valueOf(list.get(2)));

                    cell = row.createCell(3);
                    cell.setCellValue(list.get(3));

                    cell = row.createCell(4);
                    cell.setCellValue(Integer.valueOf(list.get(4)));

                    cell = row.createCell(5);
                    cell.setCellValue(list.get(5));

                    rowNum++;
                }
                wb.write(out);
//            StaticMethods.getFrameByName("RunQuestionnaireAnalyser").dispose();
                out.close();
                JOptionPane.showMessageDialog(null, Localisation.getMessage("info.filecreated") + file.getAbsolutePath() + ".xls",
                        Localisation.getMessage("info.title"), JOptionPane.INFORMATION_MESSAGE);
            } catch (java.io.FileNotFoundException fileOpen) {
                MessageFactory.errorToSave();

            } catch (IOException e) {
                e.printStackTrace();
                throw new IOException("error");
            } finally {
                out.close();
            }
        }

    }

    public static List<List<String>> getDataForCheckResultAnswers() {
        List<List<String>> dataList = new ArrayList<List<String>>();
        Connection conn;
        DbTable dbTable = new DbTable(DbUtils.GENERAL_DATABASE_NAME, "result_answers", null);
        String queryGeneral = "select " +
                DbUtils.GENERAL_DATABASE_NAME + ".result_answers.*, " +
                DbUtils.GENERAL_DATABASE_NAME + ".probationers.probationer_code," +
                DbUtils.GENERAL_DATABASE_NAME + ".dic_visits.name_ru," +
                DbUtils.GENERAL_DATABASE_NAME + ".dic_visits.name_en," +
                DbUtils.GENERAL_DATABASE_NAME + ".dic_visits.name_kk," +
                DbUtils.GENERAL_DATABASE_NAME + ".questions.question_symbol," +
                DbUtils.GENERAL_DATABASE_NAME + ".answers.name_ru as answer" +
                " from " + DbUtils.GENERAL_DATABASE_NAME + ".result_answers " +
                " inner join " + DbUtils.GENERAL_DATABASE_NAME + ".probationers" +
                " on("
                + DbUtils.GENERAL_DATABASE_NAME + ".result_answers.probationer_id=" +
                DbUtils.GENERAL_DATABASE_NAME + ".probationers.probationer_id) " +
                " inner join " + DbUtils.GENERAL_DATABASE_NAME + ".dic_visits" +
                " on("
                + DbUtils.GENERAL_DATABASE_NAME + ".result_answers.vizit_id=" +
                DbUtils.GENERAL_DATABASE_NAME + ".dic_visits.vizit_id) " +
                " inner join " + DbUtils.GENERAL_DATABASE_NAME + ".answers" +
                " on("
                + DbUtils.GENERAL_DATABASE_NAME + ".result_answers.answer_id=" +
                DbUtils.GENERAL_DATABASE_NAME + ".answers.answer_id) " +
                " inner join " + DbUtils.GENERAL_DATABASE_NAME + ".questions" +
                " on("
                + DbUtils.GENERAL_DATABASE_NAME + ".answers.question_id=" +
                DbUtils.GENERAL_DATABASE_NAME + ".questions.question_id) " +
                " inner join " + DbUtils.SECONDARY_DATABASE_NAME + ".result_answers " +
                " on(" + DbUtils.GENERAL_DATABASE_NAME + ".result_answers.result_answer_id="
                + DbUtils.SECONDARY_DATABASE_NAME + ".result_answers.result_answer_id)"+
                " and "+DbUtils.GENERAL_DATABASE_NAME+ ".result_answers.probationer_id in(" +
                " SELECT probationer_id FROM probationers" +
                " INNER JOIN groups ON (probationers.group_id=groups.group_id) " +
                " INNER JOIN dic_phase ON (groups.phase_id = dic_phase.phase_id) " +
                " WHERE dic_phase.phase_id IN("+StaticMethods.joinPhaseListToString(RunDBChecking.phaseToReturn)+")" +
                ")";

        String querySecondary = "select " +
                DbUtils.SECONDARY_DATABASE_NAME + ".result_answers.*, " +
                DbUtils.SECONDARY_DATABASE_NAME + ".probationers.probationer_code," +
                DbUtils.SECONDARY_DATABASE_NAME + ".dic_visits.name_ru," +
                DbUtils.SECONDARY_DATABASE_NAME + ".dic_visits.name_en," +
                DbUtils.SECONDARY_DATABASE_NAME + ".dic_visits.name_kk," +
                DbUtils.SECONDARY_DATABASE_NAME + ".questions.question_symbol," +
                DbUtils.SECONDARY_DATABASE_NAME + ".answers.name_ru as answer" +
                " from " + DbUtils.SECONDARY_DATABASE_NAME + ".result_answers " +
                " inner join " + DbUtils.SECONDARY_DATABASE_NAME + ".probationers" +
                " on("
                + DbUtils.SECONDARY_DATABASE_NAME + ".result_answers.probationer_id=" +
                DbUtils.SECONDARY_DATABASE_NAME + ".probationers.probationer_id) " +
                " inner join " + DbUtils.SECONDARY_DATABASE_NAME + ".dic_visits" +
                " on("
                + DbUtils.SECONDARY_DATABASE_NAME + ".result_answers.vizit_id=" +
                DbUtils.SECONDARY_DATABASE_NAME + ".dic_visits.vizit_id) " +
                " inner join " + DbUtils.SECONDARY_DATABASE_NAME + ".answers" +
                " on("
                + DbUtils.SECONDARY_DATABASE_NAME + ".result_answers.answer_id=" +
                DbUtils.SECONDARY_DATABASE_NAME + ".answers.answer_id) " +
                " inner join " + DbUtils.SECONDARY_DATABASE_NAME + ".questions" +
                " on("
                + DbUtils.SECONDARY_DATABASE_NAME + ".answers.question_id=" +
                DbUtils.SECONDARY_DATABASE_NAME + ".questions.question_id) " +
                " inner join " + DbUtils.GENERAL_DATABASE_NAME + ".result_answers " +
                " on(" + DbUtils.SECONDARY_DATABASE_NAME + ".result_answers.result_answer_id="
                + DbUtils.GENERAL_DATABASE_NAME + ".result_answers.result_answer_id)"+
                " and "+DbUtils.SECONDARY_DATABASE_NAME+ ".result_answers.probationer_id in(" +
                " SELECT probationer_id FROM probationers" +
                " INNER JOIN groups ON (probationers.group_id=groups.group_id) " +
                " INNER JOIN dic_phase ON (groups.phase_id = dic_phase.phase_id) " +
                " WHERE dic_phase.phase_id IN("+StaticMethods.joinPhaseListToString(RunDBChecking.phaseToReturn)+")" +
                ")";

        for (int i = 0; i < dbTable.getColumnNamesLinkedList().size(); i++) {
            String columnName = dbTable.getColumnNamesLinkedList().get(i);
            if (i == 0) {
                queryGeneral = queryGeneral +
                        " where " + DbUtils.GENERAL_DATABASE_NAME + "." + dbTable.getTableName() + "." + columnName +
                        "!=" + DbUtils.SECONDARY_DATABASE_NAME + "." + dbTable.getTableName() + "." + columnName;
                querySecondary = querySecondary +
                        " where " + DbUtils.GENERAL_DATABASE_NAME + "." + dbTable.getTableName() + "." + columnName +
                        "!=" + DbUtils.SECONDARY_DATABASE_NAME + "." + dbTable.getTableName() + "." + columnName;
            } else {
                queryGeneral = queryGeneral +
                        " or " + DbUtils.GENERAL_DATABASE_NAME + "." + dbTable.getTableName() + "." + columnName +
                        "!=" + DbUtils.SECONDARY_DATABASE_NAME + "." + dbTable.getTableName() + "." + columnName;
                querySecondary = querySecondary +
                        " or " + DbUtils.GENERAL_DATABASE_NAME + "." + dbTable.getTableName() + "." + columnName +
                        "!=" + DbUtils.SECONDARY_DATABASE_NAME + "." + dbTable.getTableName() + "." + columnName;
            }

        }
        try {
            conn = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps1 = conn.prepareStatement(queryGeneral+" order by result_answer_id");
            PreparedStatement ps2 = conn.prepareStatement(querySecondary+" order by result_answer_id");
            ResultSet rs1 = ps1.executeQuery();
            ResultSet rs2 = ps2.executeQuery();
            while (rs1.next()) {
                ResultSetMetaData rsmd1 = rs1.getMetaData();
                while (rs2.next()) {
                    if (rs1.getInt("result_answer_id") == rs2.getInt("result_answer_id")) {
//                        System.out.println(rs1.getInt("result_answer_id") + "=" + rs2.getInt("result_answer_id"));
                        for (int i = 1; i < rsmd1.getColumnCount() + 1; i++) {
                            if (rsmd1.getColumnName(i).equals("user")
                                    || rsmd1.getColumnName(i).equals("insert_datetime")
                                    || rsmd1.getColumnName(i).equals("modified_date")
                                    || rsmd1.getColumnName(i).equals("modified_user")
                                    || rsmd1.getColumnName(i).equals("date_of_answer")
                                    || rsmd1.getColumnName(i).equals("points")
                                    || rsmd1.getColumnName(i).equals("answer")) {
                                continue;
                            } else {
                                if (!rs1.getString(i).equals(rs2.getString(i))) {
                                    List<String> tempList = new ArrayList<String>();
                                    tempList.add(dbTable.getTableName());
                                    tempList.add(rsmd1.getColumnName(i));
                                    tempList.add(rs1.getString("result_answer_id"));
                                    tempList.add(rs1.getString(i));
                                    tempList.add(rs2.getString(i));
                                    tempList.add(rs1.getString("probationer_code"));
                                    tempList.add(rs1.getString("name_ru"));
                                    tempList.add(rs1.getString("question_symbol"));
                                    tempList.add(rs1.getString("answer"));
                                    tempList.add(rs2.getString("answer"));
                                    dataList.add(tempList);
//                                System.out.println("tableName:" + rsmd1.getTableName(i) +
//                                        " test_id_Original: " + rs1.getString("result_answer_id") +
//                                        " test_id_Copy: " + rs2.getString("result_answer_id") +
//                                        " columnName: " + rsmd1.getColumnName(i) + " columnValueOriginal: " + rs1.getString(i) +
//                                        " columnValueCopy: " + rs2.getString(i));
                                }
                            }

                        }
                        break;
                    }
                }
            }
            rs1.close();
            rs2.close();
            ps1.close();
            ps2.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return dataList;
    }

    public static void getCompareExcelForResultAnswers() throws IOException {
        List<List<String>> dataList = TestMethods.getDataForCheckResultAnswers();
        if (dataList.size() < 1) {
            MessageFactory.getMessageWarnings(false);
        } else {
            JFileChooser jfc = new JFileChooser();
            jfc.setDialogTitle(Localisation.getMessage("form.selectplaceandname"));
            jfc.showSaveDialog(null);
            jfc.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
            File file = jfc.getSelectedFile();

            FileOutputStream out = null;
            Cell cell;
            Row row;
            try {
                out = new FileOutputStream(file.getAbsolutePath() + ".xls");
                Workbook wb = new HSSFWorkbook();
                Sheet s = wb.createSheet();
                int rowNum = 1;
                row = s.createRow(0);

                cell = row.createCell(0);
                cell.setCellValue("Test name");

                cell = row.createCell(1);
                cell.setCellValue("Column Name");

                cell = row.createCell(2);
                cell.setCellValue("result_answer_id");

                cell = row.createCell(3);
                cell.setCellValue("Column value of general");

                cell = row.createCell(4);
                cell.setCellValue("Column value of secondary");

                cell = row.createCell(5);
                cell.setCellValue("Probationer Code");

                cell = row.createCell(6);
                cell.setCellValue("Day of visit");

                cell = row.createCell(7);
                cell.setCellValue("Question");

                cell = row.createCell(8);
                cell.setCellValue("Answer of general");

                cell = row.createCell(9);
                cell.setCellValue("Answer of secondary");

                for (List<String> list : dataList) {
                    row = s.createRow(rowNum);

                    cell = row.createCell(0);
                    cell.setCellValue(list.get(0));

                    cell = row.createCell(1);
                    cell.setCellValue(list.get(1));

                    cell = row.createCell(2);
                    cell.setCellValue(list.get(2));

                    cell = row.createCell(3);
                    cell.setCellValue(list.get(3));

                    cell = row.createCell(4);
                    cell.setCellValue(list.get(4));

                    cell = row.createCell(5);
                    cell.setCellValue(list.get(5));

                    cell = row.createCell(6);
                    cell.setCellValue(list.get(6));

                    cell = row.createCell(7);
                    cell.setCellValue(list.get(7));

                    cell = row.createCell(8);
                    cell.setCellValue(list.get(8));

                    cell = row.createCell(9);
                    cell.setCellValue(list.get(9));

                    rowNum++;
                }
                wb.write(out);
//            StaticMethods.getFrameByName("RunQuestionnaireAnalyser").dispose();
                out.close();
                JOptionPane.showMessageDialog(null, Localisation.getMessage("info.filecreated") + file.getAbsolutePath() + ".xls",
                        Localisation.getMessage("info.title"), JOptionPane.INFORMATION_MESSAGE);
            } catch (java.io.FileNotFoundException fileOpen) {
                MessageFactory.errorToSave();

            } catch (IOException e) {
                e.printStackTrace();
                throw new IOException("error");
            } finally {
                out.close();
            }
        }

    }

}
